package com.stable.model;

import com.stable.spider.eastmoney.EastMoneySpider;
import com.stable.spider.ths.XLSData;
import com.stable.vo.ProfitForecast;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;

import java.util.Arrays;
import java.util.Comparator;
import java.util.List;

//自由现金流折现模型
public class VAFCFF {

    public static void VAFCFFwriteXls(String code, HSSFWorkbook workbook, List<XLSData> benefitList, List<XLSData> cashList, List<XLSData> debtList) {
        HSSFSheet sheet = workbook.createSheet("自由现金流折现模型");// 创建工作表(Sheet)
        List nullList = Arrays.asList();

        HSSFUtil.row(workbook, sheet, 0, code + ":报告参数", "科目\\时间", benefitList);
        HSSFUtil.row(workbook, sheet, 1, "净利润:NI", "净利润(元)", cashList);
        HSSFUtil.row(workbook, sheet, 2, "经营现金流:CFO", "经营活动产生的现金流量净额(元)", cashList);
        HSSFUtil.row(workbook, sheet, 3, "资本支出:CAPEX", "购建固定资产、无形资产和其他长期资产支付的现金(元)", cashList);

        HSSFUtil.row(workbook, sheet, 4, "", "", nullList);
        HSSFUtil.rowFormula(workbook, sheet, 5, "净利润：亿", "CS2/100000000", benefitList, "0.00");
        HSSFUtil.rowFormula(workbook, sheet, 6, "经营现金流：亿", "CS3/100000000", cashList, "0.00");
        HSSFUtil.rowFormula(workbook, sheet, 7, "资本支出：亿", "CS4/100000000", cashList, "0.00");

        HSSFUtil.row(workbook, sheet, 8, "", "", nullList);
        HSSFUtil.rowFormula(workbook, sheet, 9, "自由现金流FCFF(简单)=经营现金流-资本支出", "(CS3-CS4)/100000000", benefitList, "0.00");
        HSSFUtil.rowFormula(workbook, sheet, 10, "资本支出/经营现金流:CAPEX/CFO", "CS4/CS3", benefitList, "0%");
        HSSFUtil.rowFormula(workbook, sheet, 11, "经营现金流/净利润:CFO/NI", "CS3/CS2", benefitList, "0%");

        HSSFUtil.row(workbook, sheet, 12, "", "", nullList);
        HSSFUtil.rowFormula(workbook, sheet, 13, "净利润增速", "(CS6-CT6)/CS6", benefitList, "0%");
        HSSFUtil.row(workbook, sheet, 14, "预测归属于母公司股东的净利润", "", nullList);


        //归属于母公司股东的净利润(元) 预测值部分;
        EastMoneySpider eastMoneySpider = new EastMoneySpider();
        List<ProfitForecast> profitForecast = eastMoneySpider.getProfitForecast(code);
        profitForecast.sort(Comparator.comparing(ProfitForecast::getRq).reversed());

        HSSFRow row15 = sheet.createRow(15);
        int[] index15 = {1};
        profitForecast.stream().forEach(data -> {
            if (StringUtils.isNotBlank(data.getJlr()) && !StringUtils.equals(data.getJlr(), "--")) {
                HSSFCell row15cell1 = row15.createCell(index15[0]);
                row15cell1.setCellValue(data.getRq());
                index15[0]++;
            }
        });
        HSSFRow row16 = sheet.createRow(16);
        int[] index16 = {1};
        profitForecast.stream().forEach(data -> {
            if (StringUtils.isNotBlank(data.getJlr()) && !StringUtils.equals(data.getJlr(), "--")) {
                HSSFCell row16cell1 = row16.createCell(index16[0]);

                String jlr = data.getJlr();
                String paramJlr = jlr.split("\\(")[0].replace("亿", "");
                row16cell1.setCellValue(paramJlr);
                index16[0]++;
            }
        });
        HSSFUtil.rowFormula(workbook, sheet, 17, "预测净利润增速", "(CS17-CT17)/CS17", benefitList, "0%");


        HSSFUtil.row(workbook, sheet, 18, "估值开始", "", nullList);


        HSSFRow row19 = sheet.createRow(19);
        HSSFUtil.rowColumn(workbook, row19, 0, "估值", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row19, 1, "前三年用市场\r\n预估数据，\r\n后7年增速为：", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row19, 2, "假设经营现\n金流=净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row19, 3, "假设资本支出\n等于经营现金流的", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row19, 4, "自由现金流对\n净利润的比值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row19, 5, "每年的折现率为：", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row19, 5, "每年的自由现\n金流折现为：", Font.COLOR_NORMAL);


        HSSFRow row20 = sheet.createRow(20);
        HSSFUtil.rowColumnFormula(workbook, row20, 1, "AVERAGE(B18:D18)", Font.COLOR_RED, "0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 2, "AVERAGE(B12:E12)", Font.COLOR_RED, "0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 3, "AVERAGE(B11:E11)", Font.COLOR_RED, "0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 4, "C21*(1-D21)", Font.COLOR_RED, "0%");
        HSSFUtil.rowColumn(workbook, row20, 5, "10%", Font.COLOR_RED);


        HSSFRow row21 = sheet.createRow(21);
        HSSFUtil.rowColumn(workbook, row21, 1, "净利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row21, 2, "经营现金流", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row21, 3, "资本支出", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row21, 4, "自由现金流FCF", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row21, 5, "折现系数", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row21, 6, "自由现金流折现", Font.COLOR_NORMAL);

        // 正式估值的内容部分
        HSSFRow row22 = sheet.createRow(22);
        HSSFUtil.rowColumnFormula(workbook, row22, 0, "B1+1", Font.COLOR_NORMAL, "");
        HSSFUtil.rowColumnFormula(workbook, row22, 1, "D17", Font.COLOR_RED, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 2, "B23*C$21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 3, "C23*D$21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 4, "C23-D23", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 5, "1/(1+F$21)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row22, 6, "E23*F23", Font.COLOR_NORMAL, "0.00");

        HSSFRow row23 = sheet.createRow(23);
        HSSFUtil.rowColumnFormula(workbook, row23, 0, "B1+2", Font.COLOR_NORMAL, "");
        HSSFUtil.rowColumnFormula(workbook, row23, 1, "C17", Font.COLOR_RED, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 2, "B24*C$21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 3, "C24*D$21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 4, "C24-D24", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 5, "F23*1/(1+F$21)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row23, 6, "E24*F24", Font.COLOR_NORMAL, "0.00");

        HSSFRow row24 = sheet.createRow(24);
        HSSFUtil.rowColumnFormula(workbook, row24, 0, "B1+3", Font.COLOR_NORMAL, "");
        HSSFUtil.rowColumnFormula(workbook, row24, 1, "B17", Font.COLOR_RED, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 2, "B25*C$21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 3, "C25*D$21", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 4, "C25-D25", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 5, "F24*1/(1+F$21)", Font.COLOR_NORMAL, "0.000");
        HSSFUtil.rowColumnFormula(workbook, row24, 6, "E25*F25", Font.COLOR_NORMAL, "0.00");


        int rowNum = 25;
        int initYear = 4;
        for (int i = 1; i <= 7; i++) {
            HSSFRow row25 = sheet.createRow(rowNum);
            HSSFUtil.rowColumnFormula(workbook, row25, 0, "B1+" + (initYear + i - 1), Font.COLOR_NORMAL, "");
            HSSFUtil.rowColumnFormula(workbook, row25, 1, "B" + rowNum + "*(1+B$21)", Font.COLOR_NORMAL, "0.00");
            HSSFUtil.rowColumnFormula(workbook, row25, 2, "B" + (rowNum + 1) + "*C$21", Font.COLOR_NORMAL, "0.00");
            HSSFUtil.rowColumnFormula(workbook, row25, 3, "C" + (rowNum + 1) + "*D$21", Font.COLOR_NORMAL, "0.00");
            HSSFUtil.rowColumnFormula(workbook, row25, 4, "C" + (rowNum + 1) + "-D" + (rowNum + 1), Font.COLOR_NORMAL, "0.00");
            HSSFUtil.rowColumnFormula(workbook, row25, 5, "F" + rowNum + "*1/(1+F$21)", Font.COLOR_NORMAL, "0.000");
            HSSFUtil.rowColumnFormula(workbook, row25, 6, "E" + (rowNum + 1) + "*F" + (rowNum + 1), Font.COLOR_NORMAL, "0.00");
            rowNum ++;
        }
//        HSSFRow row25 = sheet.createRow(25);
//        rowColumnFormula(workbook, row25, 0, "B1+4", Font.COLOR_NORMAL, "");
//        rowColumnFormula(workbook, row25, 1, "B25*(1+B$21)", Font.COLOR_NORMAL, "0.00");
//        rowColumnFormula(workbook, row25, 2, "B26*C$21", Font.COLOR_NORMAL, "0.00");
//        rowColumnFormula(workbook, row25, 3, "C26*D$21", Font.COLOR_NORMAL, "0.00");
//        rowColumnFormula(workbook, row25, 4, "C26-D26", Font.COLOR_NORMAL, "0.00");
//        rowColumnFormula(workbook, row25, 5, "F25*1/(1+F$21)", Font.COLOR_NORMAL, "0.00");
//        rowColumnFormula(workbook, row25, 6, "E26*F26", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.row(workbook, sheet, 33, "", "", nullList);
        HSSFRow row34 = sheet.createRow(34);
        HSSFUtil.rowColumn(workbook, row34, 0, "未来10年求和", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row34, 6, "SUM(G23:G32)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row35 = sheet.createRow(35);
        HSSFUtil.rowColumn(workbook, row35, 0, "永续", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row35, 1, "0%", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row35, 2, "10年以后自由现金流的增长率如左", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row35, 6, "E32/(F21-B35)*(1/(1+10%)^10)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row36 = sheet.createRow(36);
        HSSFUtil.rowColumn(workbook, row36, 0, "求和", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row36, 6, "G35+G36", Font.COLOR_NORMAL, "0.00");

        HSSFRow row38 = sheet.createRow(38);
        HSSFUtil.rowColumn(workbook, row38, 0, "总股本：亿", Font.COLOR_NORMAL);
        Double zgb = eastMoneySpider.getCapitalStockStructure(code);
        HSSFUtil.rowColumn(workbook, row38, 6, zgb +"", Font.COLOR_RED);

        HSSFRow row39 = sheet.createRow(39);
        HSSFUtil.rowColumn(workbook, row39, 0, "目标价", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row39, 6, "G37/G39", Font.COLOR_NORMAL, "0.00");
    }


}
